package rsj.dao;

import com.rlyy.aibase.db.QueryRunner;
import com.rlyy.aibase.utils.base.MapNode;
import com.rlyy.aibase.utils.base.Node;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.aibase.utils.helpers.DateHelper;
import org.apache.commons.lang3.StringUtils;
import rsj.db.dbutils.UniqueID;
import rsj.table.SYS_ORGP;
import rsj.table.SYS_ORGPTYPE;
import rsj.table.SYS_PRCO;
import rsj.table.SYS_PROD;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;

import static rsj.table.SYS_ORGP.Orgp_Orgpstatus;

public class OrgpDao extends BaseDao {

    private static final OrgpDao instance = new OrgpDao();
    private static final String tableName = "sys.sys_orgp";

    public static OrgpDao getInstance() {
        return instance;
    }

    public void orgpInsert(Connection conn, String id, String prodName, String prodId, Date ctime, String type,
                           String billmethod, Date endtime, String operson, String orgId, Date contractime, String buyamount, String prodUid) throws SQLException {

        QueryRunner queryRunner = new QueryRunner();
        String insertsql = "insert into " + SYS_ORGP.Table_Name + " (" + SYS_ORGP.Orgp_Id + "," + SYS_ORGP.Orgp_Prod_Name + "," + SYS_ORGP.Orgp_Prod_Id +
                "," + SYS_ORGP.Orgp_Ctime + "," + SYS_ORGP.Orgp_type + "," + SYS_ORGP.Orgp_Billmethod + "," + SYS_ORGP.Orgp_Endtime +
                "," + SYS_ORGP.Orgp_Operson + "," + SYS_ORGP.Orgp_Org_Id + "," + SYS_ORGP.Orgp_Contractime + "," + SYS_ORGP.Orgp_Buyamount
                + "," + SYS_ORGP.Orgp_Status + "," + Orgp_Orgpstatus + "," + SYS_ORGP.Orgp_Utime + "," + SYS_ORGP.Orgp_Rechargetime + "," + SYS_ORGP.Orgp_Prod_Uid +
                " )values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        queryRunner.update(conn, insertsql, id, prodName, prodId, ctime, type, billmethod
                , endtime, operson, orgId, contractime, Integer.valueOf(buyamount), SYS_ORGPTYPE.Orgp_Status_Ineffective
                , SYS_ORGPTYPE.Orgp_Orgpstatus_Start, DateHelper.strToTimestamp(DateHelper.getDate(new java.util.Date()))
                , DateHelper.strToTimestamp(DateHelper.getDate(new java.util.Date())), prodUid);

        //将已申请状态改为申请已处理状态

        Object[][] params = new Object[prodId.split("_").length][];
        for (int i = 0; i < prodId.split("_").length; i++) {
            Object[] arr = new Object[4];
            arr[0] = DateHelper.strToTimestamp(DateHelper.getDate(new java.util.Date()));
            arr[1] = SYS_ORGPTYPE.Orgp_Status_Apply;
            arr[2] = orgId;
            arr[3] = prodId.split("_")[i];
            params[i] = arr;
        }
        String updatesql = "update " + SYS_ORGP.Table_Name + " set " + SYS_ORGP.Orgp_Status + " = " + SYS_ORGPTYPE.Orgp_Status_ApplyOver + "," + SYS_ORGP.Orgp_Utime + "= ?" + " where " +
                SYS_ORGP.Orgp_Status + " = ? and " + SYS_ORGP.Orgp_Org_Id + "= ? and " + SYS_ORGP.Orgp_Prod_Id + " = ?";
        queryRunner.batch(conn, updatesql, params);

        //将接口的销量+1

        Object[][] params1 = new Object[prodId.split(",").length][];
        for (int i = 0; i < prodId.split(",").length; i++) {
            String[] arr = new String[1];
            arr[0] = prodId.split(",")[i];
            params1[i] = arr;
        }
        queryRunner.batch(conn, " update " + SYS_PROD.table_name + " set " + SYS_PROD.Prod_Sales + " = " + SYS_PROD.Prod_Sales + " +1 where " + SYS_PROD.Prod_Id + "= ?", params1);
    }

    public MapNode rechargeList(Connection conn, String stime, String etime, String orgId, int size, int pcount) throws SQLException {
        MapNode responsedata = new MapNode();
        QueryRunner queryRunner = new QueryRunner();
        StringBuilder countsql = new StringBuilder();
        Node node = null;
        TableNode map = null;
        countsql.append("select count(1) from ").append(SYS_ORGP.Table_Name).append(" where ").append(SYS_ORGP.Orgp_Org_Id).append(" = ? and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_Apply).append("' and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_ApplyOver).append("'").append(" and orgp_orgpstatus = '1'");
        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            countsql.append(" and (").append(SYS_ORGP.Orgp_Rechargetime).append(" between to_timestamp(?,'yyyy-mm-dd') and to_timestamp(?,'yyyy-mm-dd') )");
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId, stime, etime);
        } else {
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId);
        }

        int count = node.valueInt();
        responsedata.putNode("Prod_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("Prod_List", "");
            return responsedata;
        }

        StringBuilder findsql = new StringBuilder();
        findsql.append("select to_char(").append(SYS_ORGP.Orgp_Rechargetime).append(",'yyyy-mm-dd') Orgp_Rechargetime ,").append(SYS_ORGP.Orgp_Ctime).append(" ,")
                .append(SYS_ORGP.Orgp_Id).append(" ,").append(SYS_ORGP.Orgp_Prod_Name).append(",").append(Orgp_Orgpstatus).append(",").append(SYS_ORGP.Orgp_Buyamount).append(",")
                .append(SYS_ORGP.Orgp_Endtime).append(",").append(SYS_ORGP.Orgp_Billmethod).append(",").append(SYS_ORGP.Orgp_Operson).append(",").append(SYS_ORGP.Orgp_Prod_Uid)
                .append(",").append(SYS_ORGP.Orgp_Status).append(",").append(SYS_ORGP.Orgp_type).append(" from  ").append(SYS_ORGP.Table_Name).append(" where ")
                .append(SYS_ORGP.Orgp_Org_Id).append(" = ? and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_Apply).append("' and ")
                .append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_ApplyOver).append("'").append(" and orgp_orgpstatus = '1'");

        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            findsql.append(" and (").append(SYS_ORGP.Orgp_Rechargetime).append(" between to_timestamp(?,'yyyy-mm-dd') and to_timestamp(?,'yyyy-mm-dd') )");
        }
        findsql.append(" order by ").append(SYS_ORGP.Orgp_Rechargetime).append(" desc limit ? Offset ? ");

        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, stime, etime, size, size * (pcount - 1));
        } else {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, size, size * (pcount - 1));
        }


        responsedata.putNode("Prod_List", map.toJson());

        return responsedata;
    }

    public MapNode rechargeListAll(Connection conn, String stime, String etime, String orgId, int size, int pcount) throws SQLException {
        MapNode responsedata = new MapNode();
        QueryRunner queryRunner = new QueryRunner();
        StringBuilder countsql = new StringBuilder();
        Node node = null;
        TableNode map = null;
        countsql.append("select count(1) from ").append(SYS_ORGP.Table_Name).append(" where ").append(SYS_ORGP.Orgp_Org_Id).append(" = ? and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_Apply).append("' and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_ApplyOver).append("'");
        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            countsql.append(" and (").append(SYS_ORGP.Orgp_Rechargetime).append(" between to_timestamp(?,'yyyy-mm-dd') and to_timestamp(?,'yyyy-mm-dd') )");
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId, stime, etime);
        } else {
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId);
        }

        int count = node.valueInt();
        responsedata.putNode("Prod_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("Prod_List", "");
            return responsedata;
        }

        StringBuilder findsql = new StringBuilder();
        findsql.append("select to_char(").append(SYS_ORGP.Orgp_Rechargetime).append(",'yyyy-mm-dd') Orgp_Rechargetime ,").append(SYS_ORGP.Orgp_Ctime).append(" ,")
                .append(SYS_ORGP.Orgp_Id).append(" ,").append(SYS_ORGP.Orgp_Prod_Name).append(",").append(Orgp_Orgpstatus).append(",").append(SYS_ORGP.Orgp_Buyamount)
                .append(",").append(SYS_ORGP.Orgp_Endtime).append(",").append(SYS_ORGP.Orgp_Billmethod).append(",").append(SYS_ORGP.Orgp_Operson).append(",")
                .append(SYS_ORGP.Orgp_Prod_Uid).append(",").append(SYS_ORGP.Orgp_Status).append(",").append(SYS_ORGP.Orgp_type).append(" from  ").append(SYS_ORGP.Table_Name)
                .append(" where ").append(SYS_ORGP.Orgp_Org_Id).append(" = ? and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_Apply)
                .append("' and ").append(SYS_ORGP.Orgp_Status).append(" != '").append(SYS_ORGPTYPE.Orgp_Status_ApplyOver).append("'");

        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            findsql.append(" and (").append(SYS_ORGP.Orgp_Rechargetime).append(" between to_timestamp(?,'yyyy-mm-dd') and to_timestamp(?,'yyyy-mm-dd') )");
        }
        findsql.append(" order by ").append(SYS_ORGP.Orgp_Rechargetime).append(" desc limit ? Offset ? ");

        if (StringUtils.isNotBlank(stime) && StringUtils.isNotBlank(etime)) {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, stime, etime, size, size * (pcount - 1));
        } else {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, size, size * (pcount - 1));
        }


        responsedata.putNode("Prod_List", map.toJson());

        return responsedata;
    }




    public MapNode satisfyList(Connection conn, String orgId, String querykey, int size, int pcount) throws SQLException {
        MapNode responsedata = new MapNode();
        QueryRunner queryRunner = new QueryRunner();
        StringBuilder countsql = new StringBuilder();
        Node node = null;
        TableNode map = null;
        countsql.append("select count(1) from ").append(SYS_ORGP.Table_Name).append(" where ").append(SYS_ORGP.Orgp_Org_Id).append(" = ? and (")
                .append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_Service)
                .append("' or ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_ServiceOver).append("'")
                .append(" or ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_ServiceOver_LIMIT).append("'")
                .append(" or ( ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_Ineffective).append("' and orgp_type = '")
                .append(SYS_ORGPTYPE.Orgp_Type_Collect).append("') ").append(")")
                .append(" and orgp_orgpstatus = '1'");

        if (StringUtils.isNotBlank(querykey)) {
            countsql.append(" and ").append(SYS_ORGP.Orgp_Prod_Name).append(" like ?");
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId, "%" + querykey + "%");
        } else {
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId);
        }

        int count = node.valueInt();
        responsedata.putNode("Prod_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("Prod_List", "");
            return responsedata;
        }
        StringBuilder findsql = new StringBuilder();
        findsql.append("select ").append(SYS_ORGP.Orgp_Id).append(" ,").append(SYS_ORGP.Orgp_Prod_Name).append(" ,").append(SYS_ORGP.Orgp_Prod_Id).append(",").append(SYS_ORGP.Orgp_Billmethod).append(",")
                .append(SYS_ORGP.Orgp_Buyamount).append(",").append(Orgp_Orgpstatus).append(",").append(SYS_ORGP.Orgp_Useamount).append(",")
                .append(" case when orgp_billmethod = '1' then 0 else (").append(SYS_ORGP.Orgp_Buyamount).append("-").append(SYS_ORGP.Orgp_Useamount)
                .append(") end as Orgp_Reamount").append(",").append(SYS_ORGP.Orgp_Ctime).append(",").append(SYS_ORGP.Orgp_Endtime).append(",").append(SYS_ORGP.Orgp_Prod_Uid)
                .append(" from ").append(SYS_ORGP.Table_Name).append(" where ")
                .append(SYS_ORGP.Orgp_Org_Id).append(" = ? and (").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_Service)
                .append("' or ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_ServiceOver)
                .append("' OR ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_ServiceOver_LIMIT).append("'")
                .append(" or ( ").append(SYS_ORGP.Orgp_Status).append(" = '").append(SYS_ORGPTYPE.Orgp_Status_Ineffective).append("' and orgp_type = '").append(SYS_ORGPTYPE.Orgp_Type_Collect).append("') ").append(")")
                .append(" and ").append(Orgp_Orgpstatus).append(" = '1'");
        if (StringUtils.isNotBlank(querykey)) {
            findsql.append(" and ").append(SYS_ORGP.Orgp_Prod_Name).append(" like ?");
        }
        findsql.append(" order by ").append(SYS_ORGP.Orgp_Rechargetime).append(" desc limit ? Offset ? ");
        if (StringUtils.isNotBlank(querykey)) {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, "%" + querykey + "%", size, size * (pcount - 1));
        } else {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, size, size * (pcount - 1));
        }

        responsedata.putNode("Prod_List", map.toJson());

        return responsedata;
    }

    public MapNode probationList(Connection conn, String orgId, String querykey, int size, int pcount) throws SQLException {
        MapNode responsedata = new MapNode();
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer countsql = new StringBuffer();
        Node node = null;
        TableNode map = null;
        countsql.append("select count(1) from " + SYS_ORGP.Table_Name + " where " + SYS_ORGP.Orgp_Org_Id + " = ? " +
                " and sys_orgp.orgp_orgpstatus = '1' " +
                " and (" + SYS_ORGP.Orgp_Status + " = '").append(SYS_ORGPTYPE.Orgp_Status_Probation + "' or " + SYS_ORGP.Orgp_Status + " = '" + SYS_ORGPTYPE.Orgp_Status_ProbationOver + "'"
                + " or ( " + SYS_ORGP.Orgp_Status + " = '" + SYS_ORGPTYPE.Orgp_Status_Ineffective + "' and orgp_type = '" + SYS_ORGPTYPE.Orgp_Type_Probation + "') "
                + ")");

        if (StringUtils.isNotBlank(querykey)) {
            countsql.append(" and " + SYS_ORGP.Orgp_Prod_Name + " like ?");
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId, "%" + querykey + "%");
        } else {
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId);
        }

        int count = node.valueInt();
        responsedata.putNode("Prod_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("Prod_List", "");
            return responsedata;
        }
        StringBuffer findsql = new StringBuffer();
        findsql.append("select " + SYS_ORGP.Orgp_Id + " ," + SYS_ORGP.Orgp_Prod_Name + " ," + SYS_ORGP.Orgp_Prod_Id + "," + SYS_ORGP.Orgp_Buyamount + "," + Orgp_Orgpstatus)
                .append("," + SYS_ORGP.Orgp_Useamount + "," + "(" + SYS_ORGP.Orgp_Buyamount + "-" + SYS_ORGP.Orgp_Useamount + ") Orgp_Reamount" + "," + SYS_ORGP.Orgp_Ctime)
                .append("," + SYS_ORGP.Orgp_Endtime + "," + SYS_ORGP.Orgp_Prod_Uid + " from " + SYS_ORGP.Table_Name
                        + " where " + SYS_ORGP.Orgp_Org_Id + " = ? " +
                        " and sys_orgp.orgp_orgpstatus = '1' and (" + SYS_ORGP.Orgp_Status + " = '")
                .append(SYS_ORGPTYPE.Orgp_Status_Probation + "' or " + SYS_ORGP.Orgp_Status + " = '" + SYS_ORGPTYPE.Orgp_Status_ProbationOver + "'"
                        + " or ( " + SYS_ORGP.Orgp_Status + " = '" + SYS_ORGPTYPE.Orgp_Status_Ineffective + "' and orgp_type = '" + SYS_ORGPTYPE.Orgp_Type_Probation + "') "
                        + ")");
        if (StringUtils.isNotBlank(querykey)) {
            findsql.append(" and " + SYS_ORGP.Orgp_Prod_Name + " like ?");
        }
        findsql.append(" order by " + SYS_ORGP.Orgp_Rechargetime + " desc limit ? Offset ? ");
        if (StringUtils.isNotBlank(querykey)) {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, "%" + querykey + "%", size, size * (pcount - 1));
        } else {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, size, size * (pcount - 1));
        }

        responsedata.putNode("Prod_List", map.toJson());

        return responsedata;
    }

    /*合同ID
    机构ID
    产品ID
    产品名称
    试用、购买或申请
    状态（服务中、试用中、未生效、已申请、服务到期、试用到期）
    更新时间(状态变更时记录更新时间)
    */
    public String insertApplyStatus(String prodId, String prodUid, String orgId, String applyStatus, String prodName, Connection conn) {
        String fields = "orgp_id,orgp_org_id,orgp_prod_id,orgp_prod_name" +
                ",orgp_type,orgp_status,orgp_utime,orgp_orgpstatus,orgp_prod_uid";
        String sql = String.format(insertSql, tableName, fields, "?,?,?,?,?,?,?,?,?");
        String id = String.valueOf(UniqueID.generateId());
        getQueryRunner().update(conn, sql, id
                , orgId, prodId, prodName, SYS_ORGPTYPE.Orgp_Type_Satisfy
                , SYS_ORGPTYPE.Orgp_Status_Apply, new Timestamp(System.currentTimeMillis())
                , SYS_ORGPTYPE.Orgp_Orgpstatus_Start,prodUid
        );

        return id;
    }

    /**
     * 收藏列表
     *
     * @param conn
     * @param orgId
     * @param querykey
     * @param size
     * @param pcount
     */
    public MapNode collectionList(Connection conn, String orgId, String querykey, int size, int pcount) throws SQLException {
        MapNode responsedata = new MapNode();
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer countsql = new StringBuffer();
        Node node = null;
        TableNode map = null;

        countsql.append("select count(1) from " + SYS_PRCO.Table_Name + " where " + SYS_PRCO.Proc_Collect + "='1' and " + SYS_PRCO.Proc_Org_Id + " = ? ");
        if (StringUtils.isNotBlank(querykey)) {
            countsql.append(" and " + SYS_PRCO.Proc_Prod_Name + " like ?");
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId, "%" + querykey + "%");
        } else {
            node = queryRunner.queryLinkNode(conn, countsql.toString(), orgId);
        }
        int count = node.valueInt();
        responsedata.putNode("Prod_Count", String.valueOf(count));
        if (count == 0) {
            responsedata.putNode("Prod_List", "");
            return responsedata;
        }

        StringBuffer findsql = new StringBuffer();
        findsql.append("select " + SYS_PRCO.PROC_ID + "," + SYS_PRCO.Proc_Prod_Id + "," + SYS_PRCO.Proc_Prod_Name + ",p.prod_uid from " + SYS_PRCO.Table_Name)
                .append(" left join (SELECT pro.prod_uid,pro.prod_id FROM  sys.sys_prod pro) p on p.prod_id = sys_prco.prco_prod_id ")
                .append(" where " + SYS_PRCO.Proc_Collect + "='1' and " + SYS_PRCO.Proc_Org_Id + " = ? ");

        if (StringUtils.isNotBlank(querykey)) {
            findsql.append(" and " + SYS_PRCO.Proc_Prod_Name + " like ?");
        }
        findsql.append(" order by " + SYS_PRCO.Prco_Utime + " desc limit ? Offset ? ");

        if (StringUtils.isNotBlank(querykey)) {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, "%" + querykey + "%", size, size * (pcount - 1));
        } else {
            map = queryRunner.queryLinkTableNode(conn, findsql.toString(), orgId, size, size * (pcount - 1));
        }

        responsedata.putNode("Prod_List", map.toJson());

        return responsedata;

    }

    public void updateStartStop(Connection conn, String id, String status) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer updatesql = new StringBuffer();
        updatesql.append("update " + SYS_ORGP.Table_Name + " set " + Orgp_Orgpstatus + " = ?")
                .append(" where " + SYS_ORGP.Orgp_Id + " = ?");
        queryRunner.update(conn, updatesql.toString(), status, id);

    }


    /**
     * 定时任务，每两分钟更新一次合同表的状态
     */

    public void updateOrgpStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer updatesql = new StringBuffer();
        updatesql.append("update sys.sys_orgp set orgp_status =" +
                " case when " +
                " (orgp_type = '2' and current_date >= orgp_contractime and current_date < orgp_ctime) or (orgp_type = '2' and current_date >= orgp_contractime and current_date < orgp_ctime) " +
                " then  '30' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '2' and orgp_buyamount > orgp_useamount) " +
                " then '10' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '3' and orgp_buyamount >= orgp_useamount) " +
                " then '10' " +
                " when " +
                " orgp_type = '2' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and orgp_billmethod = '1' " +
                " then '10' " +
                " when " +
                "  orgp_type = '2'  and (current_date > orgp_endtime) " +
                " then '50'" +
                " when " +
                " orgp_type = '2' and orgp_billmethod = '2' and orgp_buyamount <= orgp_useamount " +
                " then '50'" +
                " when " +
                " orgp_type = '1' and (current_date > orgp_endtime)  " +
                " then '60'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_billmethod = '3') " +
                " then '20'" +
                " when " +
                " orgp_type = '1' and (orgp_buyamount <= orgp_useamount) " +
                " then '60'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_useamount > orgp_buyamount*0.5) " +
                " then '55'" +
                " when " +
                " orgp_type = '1' and (current_date >= orgp_ctime and current_date <= orgp_endtime) and (orgp_buyamount > orgp_useamount) " +
                " then '20'" +
                " else orgp_status end;");

//		updatesql.append("update "+SYS_ORGP.Table_Name+" set "+SYS_ORGP.Orgp_Status+" = ")
//				.append(" case ")
//
//				// 按条计费
//				.append("WHEN orgp_billmethod = '1' 				")
//				.append("AND CURRENT_DATE >= orgp_contractime       ")
//				.append("AND CURRENT_DATE < orgp_ctime THEN         ")
//				.append("'30'                                       ")
//				.append("WHEN orgp_billmethod = '1'                 ")
//				.append("AND CURRENT_DATE >= orgp_ctime             ")
//				.append("AND CURRENT_DATE <= orgp_endtime THEN      ")
//				.append("'10'                                       ")
//				.append("WHEN orgp_billmethod = '1'                 ")
//				.append("AND CURRENT_DATE > orgp_endtime  THEN      ")
//				.append("'50'                                       ")
//
//				// 包量计费
//				.append(" when " + SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Collect+"' and current_date >= "+SYS_ORGP.Orgp_Contractime+" and current_date <"+SYS_ORGP.Orgp_Ctime)
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_Ineffective+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Collect+"' and (current_date >= "+SYS_ORGP.Orgp_Ctime+" and current_date <= "+SYS_ORGP.Orgp_Endtime+") and ("+SYS_ORGP.Orgp_Buyamount+">"+SYS_ORGP.Orgp_Useamount+")")
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_Service+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Collect+"' and ((current_date > "+SYS_ORGP.Orgp_Endtime+") or ("+SYS_ORGP.Orgp_Buyamount+" <="+SYS_ORGP.Orgp_Useamount+"))")
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_ServiceOver+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Probation+"' and current_date >="+SYS_ORGP.Orgp_Contractime+" and current_date <"+SYS_ORGP.Orgp_Ctime)
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_Ineffective+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Probation+"' and (current_date >= "+SYS_ORGP.Orgp_Ctime+" and current_date <= "+SYS_ORGP.Orgp_Endtime+") and ("+SYS_ORGP.Orgp_Buyamount+">"+SYS_ORGP.Orgp_Useamount+")")
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_ProbationOver+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Probation+"' and (current_date >= "+SYS_ORGP.Orgp_Ctime+" and current_date <= "+SYS_ORGP.Orgp_Endtime+") and ("+(SYS_ORGP.Orgp_Buyamount+"*0.9")+"<"+SYS_ORGP.Orgp_Useamount+")")
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_ServiceOver_LIMIT+"'")
//				.append(" when ")
//				.append(SYS_ORGP.Orgp_type+" = '"+SYS_ORGPTYPE.Orgp_Type_Probation+"' and ((current_date > "+SYS_ORGP.Orgp_Endtime+") or ("+SYS_ORGP.Orgp_Buyamount+" <="+SYS_ORGP.Orgp_Useamount+"))")
//				.append(" then '"+SYS_ORGPTYPE.Orgp_Status_Probation+"'")
//				.append(" else orgp_status end");

        queryRunner.update(conn, updatesql.toString());

    }


    public void updateProdStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer updatesql = new StringBuffer();

        updatesql.append(" 	truncate table sys.sys_status;																									")
                .append(" 	with cte_2 as (                                                                                                                 ")
                .append(" 	 with cte_1 as (                                                                                                                ")
                .append(" 	  select                                                                                                                        ")
                .append(" 	    orgp_id,                                                                                                                    ")
                .append(" 	    orgp_org_id,                                                                                                                ")
                .append(" 	    regexp_split_to_table(orgp_prod_id, ',') as orgp_prod_id,                                                                   ")
                .append(" 	    orgp_status,                                                                                                                ")
                .append(" 	    orgp_contractime                                                                                                            ")
                .append(" 	  from sys.sys_orgp where orgp_orgpstatus = '1'                                                                                 ")
                .append(" 	 )                                                                                                                              ")
                .append(" 	 select                                                                                                                         ")
                .append(" 	  orgp_id,                                                                                                                      ")
                .append(" 	  orgp_org_id,                                                                                                                  ")
                .append(" 	  orgp_prod_id,                                                                                                                 ")
                .append(" 	  orgp_status,                                                                                                                  ")
                .append(" 	  orgp_contractime,                                                                                                             ")
                .append(" 	  row_number() over (partition by (orgp_org_id, orgp_prod_id) order by (orgp_status, orgp_contractime)) as num                  ")
                .append(" 	 from cte_1                                                                                                                     ")
                .append(" 	)                                                                                                                               ")
                .append(" 	insert into sys.sys_status                                                                                                      ")
                .append(" 	select                                                                                                                          ")
                .append(" 	 orgp_org_id,                                                                                                                   ")
                .append(" 	 orgp_prod_id,                                                                                                                  ")
                .append(" 	 orgp_status,                                                                                                                   ")
                .append(" 	 orgp_id                                                                                                                        ")
                .append(" 	from cte_2 where num = 1;                                                                                                       ");

//		updatesql.append("with cte_3 as ( with cte_2 as ( with cte_1 as ( select "+SYS_ORGP.Orgp_Id+","+SYS_ORGP.Orgp_Org_Id+",")
//				  .append(" regexp_split_to_table("+SYS_ORGP.Orgp_Prod_Id+",',') as orgp_prod_id ,"+SYS_ORGP.Orgp_Status+",")
//				  .append(SYS_ORGP.Orgp_Contractime+" from "+SYS_ORGP.Table_Name+") ")
//				  .append(" select "+SYS_ORGP.Orgp_Id+","+SYS_ORGP.Orgp_Org_Id+","+SYS_ORGP.Orgp_Prod_Id+","+SYS_ORGP.Orgp_Status+","+SYS_ORGP.Orgp_Contractime)
//				  .append(" ,row_number() over (partition by ( "+SYS_ORGP.Orgp_Org_Id+","+SYS_ORGP.Orgp_Prod_Id+") order by ("+SYS_ORGP.Orgp_Status+" , "+SYS_ORGP.Orgp_Contractime+")) as num")
//				  .append(" from cte_1 ) select "+SYS_ORGP.Orgp_Id+","+SYS_ORGP.Orgp_Org_Id+","+SYS_ORGP.Orgp_Prod_Id+","+SYS_ORGP.Orgp_Status)
//				  .append(" from cte_2 where num = 1 )")
//				  .append(" insert into sys.sys_status select "+SYS_ORGP.Orgp_Org_Id+","+SYS_ORGP.Orgp_Prod_Id+","+SYS_ORGP.Orgp_Status+","+SYS_ORGP.Orgp_Id)
//				  .append(" from cte_3 on conflict (sta_org_id, sta_prod_id)")
//				  .append(" do update set sta_status = excluded.sta_status, sta_orgp_id = excluded.sta_orgp_id");
//
        queryRunner.update(conn, updatesql.toString());

    }

    public TableNode findSysStatus(Connection conn) throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        StringBuffer findsql = new StringBuffer();

        findsql.append("SELECT s.sta_status,s.sta_orgp_id,s.sta_prod_id,a.aut_keyid,p.prod_id,p.prod_uid from sys.sys_status s " +
                "left join sys.sys_authon a on s.sta_org_id = a.aut_org_id " +
                "left join (SELECT pro.prod_uid,pro.prod_id FROM  sys.sys_prod pro) p on p.prod_id = s.sta_prod_id ");
        return queryRunner.queryLinkTableNode(conn, findsql.toString());
    }

    public Node countByOrgProCon(String orgId, String prodId, Date contractime, Connection conn) {
        String sql = "select count(1) from " + tableName
                + " where " + SYS_ORGP.Orgp_Org_Id + " = ? and " + SYS_ORGP.Orgp_Prod_Id + " = ? and " + SYS_ORGP.Orgp_Contractime + " = ? ";
        return getQueryRunner().queryLinkNode(conn, sql, orgId, prodId, contractime);
    }

    public MapNode getById(Connection conn, String orgpId) {
        String sql = String.format(select, SYS_ORGP.columnsAll, SYS_ORGP.Table_Name
                , " orgp_org_id = ? ");
        return getQueryRunner().queryLinkRow(conn, sql, orgpId);
    }
}
